import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
//import java.mysql.PreparedStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import ApacheClient.ApacheClient;
import Parser.ParseTree;
import Parser.Token;
//import java.util.Collection;
import java.util.HashSet;
//import java.util.LinkedHashSet;
import java.util.Set;
/**
 *
 * @author Aparna
 */
public class IntelliusQuery {
    private ApacheClient client = new ApacheClient();
    private int queryDelay = 0;
    private int pageDelay = 0;
    private int pDelayRange = 0;
    private int qDelayRange = 0;
    private ArrayList <String> nameList = new ArrayList<String>();
    private ArrayList <String> cityList = new ArrayList<String>();
    private ArrayList <String> stateList = new ArrayList<String>();
    private ArrayList <String> ageList = new ArrayList<String>();
    private int g =0;
    Connection conn = null;
    String userName = "root";
    String password = "admin";
    String url = "jdbc:mysql://localhost/aparna";
    
    public Connection getConn()
    {
    	
    	try
    	{
    	Class.forName ("com.mysql.jdbc.Driver").newInstance ();
        conn = DriverManager.getConnection (url, userName, password);
        System.out.println ("Database connection established");
    	}
    	
    	catch(Exception e)
    	{
    		e.printStackTrace();
    	}
    	
    	return conn;
    	  	
    	}
    	
    	
    
    public IntelliusQuery() {

    }

    public IntelliusQuery(int queryDelay, int pageDelay) {
        this.queryDelay = queryDelay;
        this.pageDelay = pageDelay;
    }

    public IntelliusQuery(int queryDelay, int pageDelay, int qDR, int pDR) {
        this.queryDelay = queryDelay;
        this.pageDelay = pageDelay;
        qDelayRange = qDR;
        pDelayRange = pDR;
    }

    protected void pageDelay() throws Exception {
        // A pointless delay
        Thread.sleep(pageDelay + (int) (Math.random() * pDelayRange));
    }

    protected void queryDelay() throws Exception {
        // A pointless delay
        Thread.sleep(queryDelay + (int) (Math.random() * qDelayRange));
    }

    // Replace unacceptable entries with valid ones
    protected String firstNameQuery(String query) {
        String result[];
        result = query.split(" ");
        return result[0];
    }

    
    protected String lastNameQuery(String query) {
        String result[];
        result = query.split(" ");
        return result[1];
    }
    
    
    public InputStream submitQuery(String query, int page) throws IOException {
    	//String prefix = "http://www.intelius.com/results.php?ReportType=1&formname=name&qf=" + firstNameQuery(query) + "+&qmi=&qn=" + lastNameQuery(query) +"&qc=&qs=IN&qz=&focusfirst=0";
    	String prefix = "http://www.intelius.com/results.php?ReportType=1&formname=name&qf=" + firstNameQuery(query) + "+&qmi=&qn=" + lastNameQuery(query) +"&qcs=&focusfirst=1";
    	//String prefix = "http://www.intelius.com/results.php?ReportType=1&formname=name&qf=" + firstNameQuery(query) + "+&qmi=&qn=" + lastNameQuery(query) +"&qc=&qs=IA&qz=&focusfirst=0";
    	String suffix = "";
        String urltext;

        //query = firstNameQuery(query) + lastNameQuery(query);

        if (page <= 1)
            urltext = prefix + suffix;
        else
            urltext = prefix + "&page=" + page + "&sz=10";
   System.out.println(urltext);
        return client.getPage(urltext);
    }

    public ArrayList<SearchResult> getResults(String query, int page) throws Exception {
       System.out.println("Query is" + query);
    	ArrayList<SearchResult> result = new ArrayList<SearchResult>();
        InputStream content = submitQuery(query, page);
        BufferedReader read = new BufferedReader(new InputStreamReader(content));
        ParseTree tree = new ParseTree(read);
        Token[] tokens = tree.getTokens();
        int pos;
        int pos2;
        int pos3;
        int pos5;
       // String lastname = content.
        
        /*String revName = new StringBuffer(query).reverse().toString();
		  String delims = "[ ]+";
			String[] lastname = revName.split(delims);
        
        */
        
        String lastname = lastNameQuery(query);
        
        
        String name = "";
       // String location;
        //String age;
        String att;
        //String summary;
        int id = 1766 + page*10;
        
        StringBuffer allLocations = new StringBuffer(200);
		StringBuffer allCity = new StringBuffer(200);
		StringBuffer city = new StringBuffer(100);
		StringBuffer state = new StringBuffer(100);

        // Get the <a ...> tags
        pos = tree.findNextTag(0, Token.A);
        System.out.println(pos);
        
        conn = getConn();
        //id = getMaxID();
       
        String insert_query = "INSERT INTO person(idperson, name, url,city,state,age) values (?,?,?,?,?,?)";
        PreparedStatement pstmt = conn.prepareStatement(insert_query);   

    	String commit_query = "commit";
       PreparedStatement commitStmt = conn.prepareStatement(commit_query); 
        
        while (pos >= 0) {
        	att = tokens[pos].valueOfAtt("class");
        	//System.out.println("att is " + att);
        	//System.out.println("pos is " + pos);
            //System.out.println("Att class " + tokens[pos].valueOfAtt("class"));
            //System.out.println("Att tag value " + tokens[pos].getTag());
        	
        	
        	if (att != null && att.equalsIgnoreCase("bold")) {
              //System.out.println("Found column ix1");
        		//while(!tree.f)
        		//pos2 = tree.findClose(pos);
        		pos2 = tree.findNextTag(pos + 1, Token.TD);
        			
        	System.out.println("Name is " + tree.getAllText(pos, pos2).toString());
                name = tree.getAllText(pos, pos2).toString();
               // pos3 = tree.findNextTag(pos2 + 1, Token.BR);
        	
                String delims = "[ ]+";
    			String[] listName =  name.split(delims);
             
    			for(int j =0;j<listName.length;j++)
    			{
    				if(listName[j].equals(lastname))
    				{
    					listName[j] = lastname + ",";
    				}
    			}
            
    			String finalName = "";
    			
    			for(int j =0;j<listName.length;j++)
    		  {
    				finalName = finalName + listName[j];
    			}
    				
    		System.out.println(finalName);		
    		nameList.add(finalName);	
    			
        	// pos3 = tree.findNextTag(pos2 + 1, Token.A);
               // System.out.println("Name is " + tree.getAllText(pos, pos2).toString());
               // summary = tree.getAllText(pos2, pos3).toString();
               // System.out.println("Name is " + summary);
                //summary = tree.getAllTextInsideToken(pos2).toString();
              /* if (!name.startsWith("Name/Aliases")) {
            	  pstmt.setInt(1, id);  
            	   pstmt.setString(2, finalName);
            	   pstmt.setString(3,"www.intellius.com");
            	   pstmt.execute();
            	   commitStmt.execute();
            	System.out.println("Name is inserted");
            	   
            	   //result.add(new SearchResult(id,name));
            	  id++; 
            	   
                }*/
            }
       pos = tree.findNextTag(pos + 1, Token.A);
        }
        
    
        
 /*pos3 = tree.findNextTag(0, Token.TD);
                     
        
        while (pos3 >= 0) {  
        String allAgeText = new String();
		
        att = tokens[pos3].valueOfAtt("class");
        if (att != null && att.equalsIgnoreCase("nameAge")) {
			
			int pos5 = tree.findClose(pos3);
			allAgeText = tree.getAllText(pos3, pos5).toString();
			System.out.println("All Age Text" + allAgeText);
		
		}
        pos3 = tree.findNextTag(pos3 + 1, Token.TD);
        
        }  */   
        
        
        
        pos3 = tree.findNextTag(0, Token.TD);
                     
        
        while (pos3 >= 0) {
        	att = tokens[pos3].valueOfAtt("class");  
        	
         	if (att != null && att.equalsIgnoreCase("column ix2")) {
            //url = tokens[pos].valueOfAtt("a");
    		//System.out.println("found column ix2");
      		
          //  String location = String(tree.getAllTextInsideToken(pos));
            //tokens[pos].getAllTextInsideToken(pos);
           
    		int pos4 = tree.findClose(pos3);
    		//int pos4 = tree.findNextTag(pos3 + 1, Token.TD);
          //  pos3 = tree.findNextTag(pos2 + 1, Token.DIV);
    		
    		allLocations = tree.getAllText(pos3, pos4);
    		//allLocations.replace(" ","");
    		//System.out.println("Original" + allLocations);
    		//System.out.println("Length is " + allLocations.length());
    		city = new StringBuffer(100);
    		
    		state = new StringBuffer(100);
    		
    		for(int k =0;k<allLocations.length()-2;k++)
    		{
    			//System.out.println("Char at " + k + allLocations.charAt(k));   			
    			//g=k;
    			if(allLocations.charAt(k) != ' ')
    			{
    				allCity.append(allLocations.charAt(k));

    			}
    		}
    		
    		//System.out.println("Without Spaces" + allCity); 
    		
    	    		
    		for(int k =0;k<allCity.length();k++)
    		{
    			//System.out.println("Char at " + k + allCity.charAt(k));   			
    			//g=k;
    			    			
    			if(allCity.charAt(k)== ',')
    			{
    				city.append(',');
    				state.append(allCity.charAt(k+1));
    				state.append(allCity.charAt(k+2));
    				state.append(',');
    				/*allCity.setCharAt(k,allCity.charAt(k+1));
    				allCity.setCharAt(k+3, ',');
    				System.out.println("Char at k+3" + allLocations.charAt(k+3));
    				System.out.println("K is" + k);*/
    				k = k+2;
    			}
    			else
    				city.append(allCity.charAt(k));
       		}
    		
    		String delimsCity = "[,]+";
			String[] cities =  city.toString().split(delimsCity);
			
	Set<String> s = new HashSet<String>();
			     
			        for (String a : cities)
			        {  s.add(a);
			        }
			        
		StringBuffer noDupsCity = new StringBuffer();
		
		for(String z : s)
		{
			//System.out.println("Z is " + z);
			noDupsCity.append(z);
			noDupsCity.append(",");
		//	System.out.println("NoDupsCity is " + noDupsCity);
		}
			        
//	noDupsCity = 	
		
		String delimsState = "[,]+";
		String[] states =  state.toString().split(delimsState);
		
		s = new HashSet<String>();
	     
        for (String a : states)
        {  s.add(a);
        }
        
StringBuffer noDupsState = new StringBuffer();

for(String z : s)
{
//System.out.println("Z is " + z);
noDupsState.append(z);
noDupsState.append(",");
//System.out.println("NoDupsState is " + noDupsState);
}

		
		
		
			        
    		if (!allCity.toString().equalsIgnoreCase("Haslivedin"))
    		{
    		cityList.add(noDupsCity.toString());
    		stateList.add(noDupsState.toString());
    		
    		//Set<String> city = removeDups(cityList.toArray());
    	     //stateList.toArray();
    		}
    		city.delete(0,city.length());
    		state.delete(0,state.length());
    		
            
    	allCity.delete(0,allCity.length());
    	  
        }        	
    	
    	 pos3 = tree.findNextTag(pos3 + 1, Token.TD);
    	
        }	
    	
   //  cityList = removeDups(cityList);
    // stateList = removeDups(stateList);
     
     
pos5 = tree.findNextTag(0, Token.P);
                     
        
        while (pos5 >= 0) {
        	att = tokens[pos5].valueOfAtt("class"); 
        
        if (att != null && att.equalsIgnoreCase("nameAge")) {
           // System.out.println("Name Age found");
            	
        
        StringBuffer allAgeText = new StringBuffer();
        int pos6 = tree.findClose(pos5);
		//int pos4 = tree.findNextTag(pos3 + 1, Token.TD);
      //  pos3 = tree.findNextTag(pos2 + 1, Token.DIV);
		
		StringBuffer allText = tree.getAllText(pos5, pos6);
		for(int k =0;k<allText.length();k++)
		{
			//System.out.println("Char at " + k + allLocations.charAt(k));   			
			//g=k;
			if(allText.charAt(k) != ' ')
			{
				allAgeText.append(allText.charAt(k));

			}
		}
		//allText.replaceAll(" ", "");
		
		if(!allAgeText.toString().contains("."))
				{
			System.out.println(". not found");
	             if(allAgeText.toString().contains("age"))
	             {
	            	 System.out.println("All text" + allAgeText);
	            	 int l = allAgeText.length();
	            	 ageList.add(allAgeText.substring((l-2), l));
	            	 System.out.println("Age" + allAgeText.substring((l-2), l));  
	             }
	             else
	            	 ageList.add("");
				}
		allAgeText.delete(0,allAgeText.length());
		allText.delete(0,allAgeText.length());
		
        }
        pos5 = tree.findNextTag(pos5 + 1, Token.P);
        } 
        
        
      
       	while(g<nameList.size())
       	{
       		pstmt.setInt(1, id);  
           	   pstmt.setString(2, nameList.get(g));
           	   pstmt.setString(3,"www.intellius.com");	   
     	 pstmt.setString(4,cityList.get(g));
     	 pstmt.setString(5,stateList.get(g));
     	 pstmt.setString(6,ageList.get(g));
     	pstmt.execute();
     	 commitStmt.execute();
     	System.out.println("Added Name" + nameList.get(g));
       	System.out.println("Added ID " + id);
     	 
     	 g++;
       	
      	 id++;
       	
       	}
     	 //  result.add(new SearchResult(name, summary));
       	 
       	//break;
      /* 	
        	
        	
        	
        	/*if (att != null && att.equalsIgnoreCase("column ix1")) {
                //url = tokens[pos].valueOfAtt("a");
                      
                //String age = tree.getAllTextInsideToken(pos));
               // tokens[pos].getAllTextInsideToken(pos);
                pos2 = tree.findNextTag(pos + 1, Token.DIV);
                pos3 = tree.findNextTag(pos2 + 1, Token.DIV);
                summary = tree.getAllText(pos2, pos3).toString();
               
            }*/
      

        read.close();

        return result;
    }

    public ArrayList<SearchResult> getResults(String query, int startPage, int endPage) throws Exception {
        ArrayList<SearchResult> result = new ArrayList<SearchResult>();

        for (int c = startPage; c <= endPage; c++) {
            result.addAll(getResults(query, c));
        }

        return result;
    }

    public ArrayList<SearchResult> getResultsWithDelay(String query, int startPage, int endPage) throws Exception {
        ArrayList<SearchResult> result = new ArrayList<SearchResult>();

        for (int c = startPage; c <= endPage; c++) {
            result.addAll(getResults(query, c));
            pageDelay();
        }

        return result;
    }
   
    
    public static ArrayList<String> removeDups (ArrayList<String> a){
 	   
    	for(int k=0;k<a.size();k++)
    	{
    		String delims = "[,]+";
			String[] noDups =  a.get(k).split(delims);
    		
    		String s = deDuplicate(noDups);
    		a.remove(k);
    		a.add(k, s);
    	}
    	
 	return a;
    }
    
    public static String deDuplicate(String[] test){
 	  
 	    String deduplicated = new String();
    	Set<String> s = new HashSet<String>();
 	      
 	        for (String a : test)
 	            if (!s.add(a))
 	             System.out.println("Duplicate detected: " + a);
            
 	        for(String x: s)
 	        	deduplicated.concat(x.toString());
 	   
 	return deduplicated;
    }
    
    
    
    
    public int getMaxID()
    {
    	//conn = getConn();
    	int maxid = 0;
    	try
    	{
    	Statement stmt = conn.createStatement();
		String query = "select max(idperson) from person";
		   //executing query and get the maximum id
		System.out.println(query);
		   ResultSet rs1 = stmt.executeQuery(query);
		   rs1.next();
		   System.out.println("Rs1" + rs1.next());
		   System.out.println(rs1.getInt("idperson"));
		   maxid = rs1.getInt("idperson");
    	}
    	catch(SQLException se)
    	{
    		se.printStackTrace();
    	}
		   
		   return maxid;
    }
    
    
}


